<?php

namespace App\Modules\OfficialAccount\Models;

use DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Http\Request;

class OfficialAccountMonthlyData extends Model
{
    protected $table = 'promotion_groups_monthly_send_orders';

    protected $fillable = ['id', 'month', 'agent_name', 'charge_amount', 'charge_amount_in_30_days', 'charge_amount_in_72_hours', 'cost', 'distribution_channel_id',
        'new_subscribe_num', 'promotionRegUserRechargeAmountIn72hours', 'promotionRegUserRechargeAmountInOneMonth',
        'promotionRegUserRechargeAmountInThreeMonth', 'promotionRegUserRechargeAmountInTwoMonth', 'promotion_channel', 'promotion_point', 'total_subscribe_num'];

    static function search($params = [], $isAll = false)
    {
        $search_object = self::orderBy('month', 'desc');

        if (isset($params['start_date']) && $params['start_date']) {
            $search_object->where('month', '>=', $params['start_date']);
        }
        if (isset($params['end_date']) && $params['end_date']) {
            $search_object->where('month', '<=', $params['end_date']);
        }

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            $search_object->where('distribution_channel_id', $params['distribution_channel_id']);
        }

        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $search_object->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
        }

        if (isset($params['agent_name']) && $params['agent_name']) {
            $search_object->where('agent_name', 'like', '%' . $params['agent_name'] . '%');
        }

        if (isset($params['promotion_point']) && $params['promotion_point']) {
            $search_object->where('promotion_point', $params['promotion_point']);
        }

        if (isset($params['orderBy']) && $params['orderBy']) {
            $orderType = 'desc';
            if (isset($params['orderType']) && $params['orderType']) {
                //1为正序
                $orderType = $params['orderType'];
            }
            $search_object->orderBy($params['orderBy'], $orderType);
        }

        $search_object->select('month', 'cost as totalCost', 'charge_amount as total_charge_amount',
            'new_subscribe_num as total_new_subscribe_num', 'distribution_channel_id', 'charge_amount_in_30_days', 'charge_amount_in_72_hours',
            'promotion_point', 'agent_name', 'promotionRegUserRechargeAmountInThreeMonth', 'promotionRegUserRechargeAmountInTwoMonth',
            'total_subscribe_num', 'promotionRegUserRechargeAmountIn72hours', 'promotionRegUserRechargeAmountInOneMonth'
            , 'promotion_point');
        $search_object->groupBy('month');
        $search_object->groupBy('distribution_channel_id');
        \Log::info($search_object->toSql());
        if ($isAll) {
            return $search_object->get();
        } else {
            return $search_object->paginate();
        }
    }

    static function getMonthlySum($params) {
        $search_object = self::orderBy('month', 'desc');
        if (isset($params['start_date']) && $params['start_date']) {
            $search_object->where('month', '>=', $params['start_date']);
        }else{
            $search_object->where('month','>=',date('Y-m-01'));
        }
        if (isset($params['end_date']) && $params['end_date']) {
            $search_object->where('month', '<=', $params['end_date']);
        }

        if (isset($params['distribution_channel_id']) && $params['distribution_channel_id']) {
            $search_object->where('distribution_channel_id', $params['distribution_channel_id']);
        }

        if (isset($params['distribution_channel_ids']) && $params['distribution_channel_ids']) {
            $search_object->whereIn('distribution_channel_id', $params['distribution_channel_ids']);
        }

        if (isset($params['agent_name']) && $params['agent_name']) {
            $search_object->where('agent_name', 'like', '%' . $params['agent_name'] . '%');
        }

        if (isset($params['promotion_point']) && $params['promotion_point']) {
            $search_object->where('promotion_point', $params['promotion_point']);
        }

        if (isset($params['orderBy']) && $params['orderBy']) {
            $orderType = 'desc';
            if (isset($params['orderType']) && $params['orderType']) {
                //1为正序
                $orderType = $params['orderType'];
            }
            $search_object->orderBy($params['orderBy'], $orderType);
        }

        $search_object->select(DB::raw('sum(cost) as totalCost ,sum(charge_amount) as total_charge_amount,
            sum(new_subscribe_num) as total_new_subscribe_num,sum(charge_amount_in_30_days) as charge_amount_in_30_days,
            sum(charge_amount_in_72_hours) as charge_amount_in_72_hours,
            promotion_point,agent_name,sum(promotionRegUserRechargeAmountInThreeMonth) as promotionRegUserRechargeAmountInThreeMonth,
            sum(promotionRegUserRechargeAmountInTwoMonth) as promotionRegUserRechargeAmountInTwoMonth,
            sum(total_subscribe_num) as total_subscribe_num,
            sum(promotionRegUserRechargeAmountIn72hours) as promotionRegUserRechargeAmountIn72hours,
            sum(promotionRegUserRechargeAmountInOneMonth) as promotionRegUserRechargeAmountInOneMonth'
            ));
        return $search_object->first();
    }
}
